Setting up failover

This topic explains how to set up PostgreSQL and Replicateto support a failover scenario. The procedure you need to perform depends on your specific PostgreSQL failover setup.

If the database object ID for the primary and secondary database remains the same after a failover (which is usually the case), you only need to update the hosts file on the Replicate Server machine with the secondary database server's IP address. However, if the database object ID of the secondary database is not the same as the primary database after a failover, additional configuration is required. It's usually not possible to know beforehand whether the object ID will change, but if you do know, perform the Setting up failover when the object ID for the primary and secondary database is different procedure described below. If you do not know whether the object ID will change, we recommend performing the simpler procedure described in Setting up failover when the object ID for the primary and secondary database is the same, as the object ID will usually remain the same after a failover.

If you performed the simpler setup and Replicate fails to recover during failover, then you will need to perform the Setting up failover when the object ID for the primary and secondary database is different procedure.

Setting up failover when the object ID of the primary and secondary database is the same

When a failover occurs, the Replicate task will encounter a recoverable error and will try to reconnect to the IP address of the primary database server. However, it will usually not succeed as the secondary database server will (almost always) have a different IP address.

If the switch of IP addresses is not handled by a DNS failover service, you will need to specify the new IP address in the hosts file on the Replicate Server machine. When Replicate Server is running on Windows, the path to the hosts file is C:\Windows\System32\drivers\etc\hosts. When Replicate Server is running on Linux, the path to the hosts file is /etc/hosts. After the hosts file has been updated, the Replicate task will resume automatically.

Setting up failover when the object ID of the primary and secondary database is different

In standard operation, Replicate creates a slot automatically when replicating from a PostgreSQL source. The slot names contain the object ID of the database in its name. In rare cases, the object ID of the primary and secondary databases will be different. In such a case, the Replicate task will fail during a failover as it will not be able to find the slot it created for the primary database in the secondary database. In this case, the following error will occur:

[SOURCE_CAPTURE ]E: Can't resume task after replication slot was dropped. [1020101] (postgres_endpoint_capture.c:445)

If you encounter such an error, do the following:

1. In the endpoint settings Advanced tab, add the internal parameter slotName and set the slot name value.

   The specified slotName must already exist in the primary PostgreSQL server. You can either create a new slot or use an existing slot.

   To create a new slot and retrieve its LSN, run the following query:

   SELECT lsn FROM pg_create_logical_replication_slot ('user_provided_slot_name', 'test_decoding');

   For a list of available slots and their LSNs, run the following query:

   SELECT slot_name, restart_lsn FROM pg_replication_slots WHERE slot_type = 'logical';

2. Enter the LSN for the specified slotName in the Advanced Run Options dialog's Source change position field and then start the Replicate task.

   If a failover occurs, the secondary server will take over almost immediately and become the primary server. The failover should be handled by pg_failover_slots extension.

3. When a failover occurs, the Replicate task will encounter a recoverable error and will try to reconnect to the IP address of the primary database server. However, it will usually not succeed as the secondary database server will (almost always) have a different IP address.If the switch of IP addresses is not handled by a DNS failover service, you will need to specify the new IP address in the hosts file on the Replicate Server machine. When Replicate Server is running on Windows, the path to the hosts file is C:\Windows\System32\drivers\etc\hosts. When Replicate Server is running on Linux, the path to the hosts file is /etc/hosts. After the hosts file has been updated, the Replicate task will resume automatically.